package com.zlyx.easy.core.sql;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;

import com.zlyx.easy.core.buffer.EasyBuffer;
import com.zlyx.easy.core.map.Maps;
import com.zlyx.easy.core.tool.StringFormat;
import com.zlyx.easy.core.utils.ObjectUtils;

/**
 * <p>
 * Sql构建工具
 * </p>
 *
 * @author 赵光
 * @since 2019年8月22日
 */
public class SqlBuilder {

	public enum OpType {
		SELECT, COUNT, INSERT, DELETE, UPDATE, CREATE;
	}

	public final static Map<SqlBuilder.OpType, String> sqlTemplates;

	public final static String SEPARATOR = ", ";

	static {
		sqlTemplates = Maps.newMap();
		sqlTemplates.put(OpType.SELECT, "SELECT * FROM %s WHERE %s");
		sqlTemplates.put(OpType.COUNT, "SELECT COUNT(*) FROM %s WHERE %s");
		sqlTemplates.put(OpType.INSERT, "INSERT INTO %s SET %s");
		sqlTemplates.put(OpType.DELETE, "DELETE FROM %s WHERE %s");
		sqlTemplates.put(OpType.UPDATE, "UPDATE %s SET %s");
		sqlTemplates.put(OpType.CREATE,
				"CREATE TABLE IF NOT EXISTS %s ( id bigint(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,\n%s)");
	}

	/**
	 * 创建表sql
	 * 
	 * @param tableEntity
	 * @return
	 */
	public static String createSQL(TableEntity te) {
		String tableName = te.getTableName();
		EasyBuffer eb = EasyBuffer.newBuffer();
		for (TableColumn tableColumn : te.getColumns()) {
			eb.append(tableColumn).append(",\n");
		}
		return String.format(sqlTemplates.get(OpType.CREATE), tableName, eb.clear(2)).trim();
	}

	/**
	 * 构建包含“=”的SQL
	 * 
	 * @param type      操作类型
	 * @param tableName 表名称
	 * @param params    参数map
	 * @param ignores   忽略的keys
	 * @return
	 */
	public static String buildSQL(OpType type, String tableName, Map<String, ?> conditionMap, List<String> ignoreKeys) {
		String separator = SEPARATOR;
		if (OpType.SELECT == type || OpType.COUNT == type || OpType.DELETE == type) {
			separator = " AND ";
		}
		String condition = appendSQL(conditionMap, separator, " = ", ignoreKeys);
		String sql = String.format(sqlTemplates.get(type), tableName, condition).trim();
		if (sql.endsWith("WHERE")) {
			sql = sql.substring(0, sql.indexOf("WHERE"));
		}
		return sql.trim();
	}

	/**
	 * 拼接equals参数
	 * 
	 * @param params    参数map
	 * @param separator 分隔符
	 * @param sysbol    连接符(eg:>,<,=等)
	 * @param ignores   忽略的keys
	 * @return
	 */
	public static String appendSQL(Map<String, ?> params, String separator, String sysbol, List<String> ignoreKeys) {
		if (params == null) {
			return "";
		}
		EasyBuffer eb = new EasyBuffer();
		for (Entry<String, ?> entry : params.entrySet()) {
			if (ignoreKeys == null
					|| (!ignoreKeys.contains(entry.getKey()) && ObjectUtils.isNotEmpty(entry.getValue()))) {
				eb.append(entry.getKey()).append(sysbol)
						.append(isInteger(entry.getValue()) ? "" + entry.getValue() : "'" + entry.getValue() + "'")
						.append(separator);
			}
		}
		return eb.clear(separator.length());
	}

	/**
	 * 构建包含like语句的SQL
	 * 
	 * @param type        操作类型
	 * @param params      参数map
	 * @param sql         sql
	 * @param includeKeys 包含的keys
	 * @return
	 */
	public static String likeSQL(OpType type, String tableName, Map<String, ?> conditionMap) {
		EasyBuffer likeSQL = new EasyBuffer();
		List<String> ignoreKeys = new ArrayList<>();
		if (conditionMap != null) {
			for (Entry<String, ?> entry : conditionMap.entrySet()) {
				if (entry.getValue() != null) {
					String value = "" + entry.getValue();
					if (value.startsWith("%") || value.endsWith("%")) {
						ignoreKeys.add(entry.getKey());
						likeSQL.append(" ", entry.getKey(), " LIKE '", value, "' AND");
					}
				}
			}
		}
		String sql = SqlBuilder.buildSQL(type, tableName, conditionMap, ignoreKeys);
		if (sql.contains(" WHERE")) {
			sql += " AND";
		} else {
			sql += " WHERE";
		}
		return sql + likeSQL.clear(3);
	}

	/**
	 * @param update
	 * @param table
	 * @param params
	 * @return
	 */
	public static String updateSQL(String table, Map<String, ?> updateMap, Map<String, ?> conditionMap) {
		EasyBuffer eb = EasyBuffer.newBuffer(SqlBuilder.buildSQL(OpType.UPDATE, table, updateMap, null), " WHERE ");
		for (Entry<String, ?> entry : conditionMap.entrySet()) {
			if (entry.getValue() != null) {
				if (!eb.toString().endsWith("WHERE ")) {
					eb.append("AND ");
				}
				String value = "" + entry.getValue();
				if (value.startsWith("%") || value.endsWith("%")) {
					eb.append(entry.getKey(), " LIKE '", value, "'");
				} else if (isInteger(entry.getValue())) {
					eb.append(entry.getKey(), " = ", value);
				} else {
					eb.append(entry.getKey(), " = '", value, "'");
				}
			}
		}
		String sql = eb.toString();
		return sql.endsWith("WHERE") ? sql.substring(0, sql.indexOf("WHERE")).trim() : sql;
	}

	public static boolean isInteger(Object o) {
		Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");
		return pattern.matcher(o + "").matches();
	}

	/**
	 * 构建排序片段
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public static String order(String[] ascs, String[] descs) {
		if (ascs == null) {
			if (descs == null) {
				return "";
			} else {
				return desc(descs);
			}
		} else {
			if (descs == null) {
				return asc(ascs);
			} else {
				return EasyBuffer.wrapper(" ORDER BY ", StringFormat.format(ascs), " ASC, ", StringFormat.format(descs),
						" DESC");
			}
		}
	}

	/**
	 * 构建排序片段
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public static String asc(String... ascs) {
		return EasyBuffer.wrapper(" ORDER BY ", StringFormat.format(ascs), " ASC");
	}

	/**
	 * 构建排序片段
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public static String desc(String... descs) {
		return EasyBuffer.wrapper(" ORDER BY ", StringFormat.format(descs), " DESC");
	}

	/**
	 * 构建分页片段
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public static String page(int pageNumber, int pageSize) {
		int start = (pageNumber - 1) * pageSize;
		int end = start + pageSize;
		return EasyBuffer.wrapper(" LIMIT ", start, ",", end);
	}

	/**
	 * 构建分页片段
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public static String page(String pageNumber, String pageSize) {
		return page(Integer.parseInt(pageNumber), Integer.parseInt(pageSize));
	}

	public static void main(String[] args) {
		Map<String, String> params = Maps.newMap("name", "%测试1%", "id", 1);
		System.out.println(SqlBuilder.likeSQL(OpType.SELECT, "test", params)
				+ order(new String[] { "name", "id" }, new String[] { "age" }));
		System.out.println(SqlBuilder.likeSQL(OpType.COUNT, "test", params));
		System.out.println(SqlBuilder.likeSQL(OpType.DELETE, "test", params));
		System.out.println(SqlBuilder.likeSQL(OpType.DELETE, "test", params));

		Map<String, String> updateMap = Maps.newMap("name", "测试2", "phone", "123456789");
		Map<String, Integer> conditionMap = Maps.newMap("id", 1);
		System.out.println(SqlBuilder.updateSQL("test", updateMap, conditionMap));
	}

}
